const sqlite3 = require('sqlite3').verbose();

// 创建一个新的数据库连接
let db = new sqlite3.Database('./instance/expresser.sqlite'); // 使用内存数据库，你也可以指定一个文件路径

// 创建一个表
db.serialize(() => {
  db.exec(`
    DROP TABLE IF EXISTS user;
    DROP TABLE IF EXISTS post;

    CREATE TABLE user (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      username TEXT UNIQUE NOT NULL,
      password TEXT NOT NULL
    );

    CREATE TABLE post (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      author_id INTEGER NOT NULL,
      created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      title TEXT NOT NULL,
      body TEXT NOT NULL,
      FOREIGN KEY (author_id) REFERENCES user (id)
    );
  `, (err) => {
    if (err) {
      console.error(err.message);
    }
    console.log('Multiple statements executed successfully');
  });
});

// 关闭数据库连接
db.close((err) => {
  if (err) {
    console.error(err.message);
  }
  console.log('Close the database connection.');
});
